﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using EffiProzDB.Persist;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TestViewAsterisks
    /// </summary>
    [TestClass]
    public class TestViewAsterisks : BaseTest
    {
        public TestViewAsterisks()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestMethod1()
        {
            setupDatabase();

            checkSimpleViews();
            checkAsterisksCombined();
            checkMultipleTables();
            checkSubSelects();
            checkColumnLists();
           checkViewsOnViews();
           checkUnionViews();

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN");
        }

        /**
    * creates the database tables needed for the test
    */
        private void setupDatabase()
        {

            try
            {
                CleanUp("TestViewAsterisks");
                sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestViewAsterisks",
                       user, password, props);

                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "DROP TABLE IF EXISTS ABC CASCADE");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "DROP TABLE IF EXISTS TABLE_A CASCADE");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "DROP TABLE IF EXISTS TABLE_B CASCADE");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy,
                    "CREATE TABLE ABC (ID INTEGER NOT NULL PRIMARY KEY, A VARCHAR(50), B VARCHAR(50), C VARCHAR(50))");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO ABC VALUES (1, 'a', 'b', 'c')");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO ABC VALUES (2, 'd', 'e', 'f')");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy,
                    "CREATE TABLE TABLE_A (ID_A INTEGER NOT NULL PRIMARY KEY, NAME_A VARCHAR(50))");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO TABLE_A VALUES (1, 'first A')");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO TABLE_A VALUES (2, 'second A')");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy,
                    "CREATE TABLE TABLE_B (ID_B INTEGER NOT NULL PRIMARY KEY, NAME_B VARCHAR(50))");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO TABLE_B VALUES (1, 'first B')");
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO TABLE_B VALUES (2, 'second B')");
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.ToString());
            }
        }

        /**
 *  creates a view with the given name, the given constituting statement, and an optional column list
 *
 *  @param viewName
 *      specifies the name of the view to create
 *  @param columnList
 *      list of names of the columns of the view, will be specified in the CREATE VIEW statement. Might be <code>null</code>.
 *  @param viewStatement
 *      the statement of the view
 */
        private void createView(String viewName, String[] columnList,
                                String viewStatement)
        {

            StringBuilder colList = new StringBuilder();

            if (columnList != null)
            {
                colList.Append(" (");

                for (int i = 0; i < columnList.Length; ++i)
                {
                    colList.Append('"').Append(columnList[i]).Append('"');

                    if (i < columnList.Length - 1)
                    {
                        colList.Append(',');
                    }
                }

                colList.Append(")");
            }

            Result resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "CREATE VIEW " + viewName + colList.ToString()
                             + " AS " + viewStatement);

            if (columnList != null)
            {
                ensureTableColumns( viewName, columnList);
            }
        }

        /**
    *  retrieves the statement which defines a given view
    */
        private String getViewStatement(String viewName)
        {

            Result res = TestHelper.ExecuteSelectSQL(sessionProxy,
                "SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.SYSTEM_VIEWS WHERE TABLE_NAME = '"
                + viewName + "'", 1);

            Iterator it = res.iterator();
            object[] obj = (object[])it.next();

            String statement = (string)obj[0];

            return statement;
        }

        /**
     *  ensures two tables (or views, that is) have the same content
     */
        private void ensureEqualContent(String tableNameLHS,
                                        String tableNameRHS)
        {

            Result lhs = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT * FROM \""
                + tableNameLHS + "\"", null);
            Result rhs = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT * FROM \""
                + tableNameRHS + "\"", null);
            Result.ResultMetaData meta = lhs.metaData;

            // lhs should be after last, rhs still on last
            Assert.AreEqual(lhs.getSize(), rhs.getSize(), "row count does not match: " + tableNameLHS + "-"
                       + tableNameRHS);

            TestHelper.MatchSingleResults(lhs, rhs, lhs.getColumnCount());

        }

        /**
   *  ensures the content of a given table matches a given object array's content
   */
        private void ensureTableContent(String tableName,
                                        Object[][] tableData)
        {

            Result lhs = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT * FROM \""
                + tableName + "\"", tableData.Length);
            Result.ResultMetaData meta = lhs.metaData;
            int colCount = meta.colLabels.Length;



            for (int count = 0; count < lhs.getSize(); count++)
            {
                TestHelper.MatchSingleResultRow(lhs, count, tableData[count]);
            }
        }

        /**
        *  ensures that a given table has columns with a given name
        */
        private void ensureTableColumns(String tableName,
                                        String[] columnNames)
        {

            Result resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT * FROM " + tableName + ";", null);

            Result.ResultMetaData meta = resultIn.metaData;

            for (int i = 0; i < resultIn.getColumnCount(); i++)
            {
                Assert.AreEqual(meta.colNames[i], columnNames[i],
                    "unexpected column name in table \"" + tableName
                    + "\" at position "
                    + i);
            }


            Assert.AreEqual(columnNames.Length, resultIn.getColumnCount(), "not enough columns in table \"" + tableName + "\""
                         );
        }


        private void checkViewTranslationAndContent(String viewName,
                  String[] columnList, String viewStatement,
                  String expectedTranslatedStatement,
                  Object expectedContent)
        {

            createView(viewName, columnList, viewStatement);

            String actualTranslatedStatement = getViewStatement(viewName);

            if (!actualTranslatedStatement.Equals(expectedTranslatedStatement))
            {
                StringBuilder message = new StringBuilder();

                message.Append(viewName).Append(
                    "'s statement not translated as expected\n");
                message.Append("original statement:\n  ").Append(
                    viewStatement).Append('\n');
                message.Append("expected translated statement:\n  ").Append(
                    expectedTranslatedStatement).Append('\n');
                message.Append("actual translated statement:\n  ").Append(
                    actualTranslatedStatement).Append('\n');

                Assert.Fail(message.ToString());
            }

            if (expectedContent != null)
            {
                if (expectedContent is String)
                {
                    ensureEqualContent(viewName, (String)expectedContent);
                }
                else
                {
                    ensureTableContent(viewName, (Object[][])expectedContent);
                }
            }
        }


  /**
 *  checks views selecting an asterisk from a table, in different flavours
 */
        private void checkSimpleViews()
        {

            // ................................................................
            // SELECT *
            checkViewTranslationAndContent(
                "S1", null, "SELECT * FROM ABC",
                "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
                "ABC");

            // adding a column to "ABC" should succeed, and still leave the view with the columns
            // before the addition
            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "ALTER TABLE ABC ADD COLUMN D VARCHAR(50)");
            ensureTableColumns("ABC", new String[] {
            "ID","A", "B", "C", "D"
        });
            ensureTableColumns("S1", new String[] {
            "ID", "A", "B", "C"
        });

            // dropping the column which is not part of the view should be possible
            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "ALTER TABLE ABC DROP COLUMN D");

            // dropping another column which *is* part of the view shouldn't
            TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, "ALTER TABLE ABC DROP COLUMN C");

            // finally, dropping the column with CASCADE should work, and also drop the view
            //executeStatement("ALTER TABLE ABC DROP COLUMN C CASCADE");
            // DROP COLUMN c CASCADE not implemented, yet
            // ................................................................
            // same as S1, but this time add a LIMIT clause to the statement
            checkViewTranslationAndContent(
                "S2", null, "SELECT LIMIT 0 2 * FROM ABC ORDER BY ID",
                "SELECT LIMIT 0 2  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC ORDER BY ID",
                "ABC");

            // ................................................................
            // same as S1, but this time add a TOP clause to the statement
            checkViewTranslationAndContent(
                "S3", null, "SELECT TOP 2 * FROM ABC ORDER BY ID",
                "SELECT TOP 2  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC ORDER BY ID",
                "ABC");

            // ................................................................
            // same as S1, but this time add a DISTINCT clause to the statement
            checkViewTranslationAndContent(
                "S4", null, "SELECT DISTINCT * FROM ABC",
                "SELECT DISTINCT ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
                "ABC");

            // ................................................................
            // same as S1, but this time qualifying the asterisk
            checkViewTranslationAndContent(
                "S5", null, "SELECT ABC.* FROM ABC",
                "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
                "ABC");

            // ................................................................
            // same as S5, but this time also giving the table an alias
            checkViewTranslationAndContent(
                "S6", null, "SELECT \"A\".* FROM ABC AS A",
                "SELECT  \"A\".ID,\"A\".A,\"A\".B,\"A\".C  FROM ABC AS A", "ABC");

            // ................................................................
            // same as S1, but bracket the SELECT definition
            checkViewTranslationAndContent(
                "S7", null, "( SELECT * FROM ABC )",
                "( SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC )",
                "ABC");
        }

           /**
     *  checks views selecting an asterisk plus existing columns
     */
    private void checkAsterisksCombined() {

        // ................................................................
        checkViewTranslationAndContent(
            "C1", null, "SELECT *, A AS \"a2\" FROM ABC",
            "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C , A AS \"a2\" FROM ABC",
            new Object[][] {
            new Object[] {
               1, "a", "b", "c", "a"
            }, new Object[] {
                2, "d", "e", "f", "d"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "C2", null, "SELECT B AS \"b2\", * FROM ABC",
            "SELECT B AS \"b2\", ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
            new Object[][] {
            new Object[] {
                "b", 1, "a", "b", "c"
            }, new Object[] {
                "e", 2, "d", "e", "f"
            }
        });
    }
/**
     *  checks views selecting asterisks from multiple tables
     */
    private void checkMultipleTables()  {

        // ................................................................
        checkViewTranslationAndContent(
            "M1", null, "SELECT * FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_A.ID_A,TABLE_A.NAME_A,TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                (1), "first A", (1), "first B"
            }, new Object[] {
                (1), "first A", (2), "second B"
            }, new Object[] {
                (2), "second A", (1), "first B"
            }, new Object[] {
                (2), "second A", (2), "second B"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "M2", null, "SELECT TABLE_B.*, TABLE_A.* FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_B.ID_B,TABLE_B.NAME_B , TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                (1), "first B", (1), "first A"
            }, new Object[] {
                (2), "second B", (1), "first A"
            }, new Object[] {
                (1), "first B", (2), "second A"
            }, new Object[] {
                (2), "second B", (2), "second A"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "M3", null, "SELECT \"TABLE_A\".* FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                (1), "first A"
            }, new Object[] {
                (1), "first A"
            }, new Object[] {
                (2), "second A"
            }, new Object[] {
                (2), "second A"
            }
        });
    }

          /**
     *  checks views selecting from sub selects
     */
    private void checkSubSelects()  {

        // ................................................................
        checkViewTranslationAndContent(
            "Q1", null, "SELECT * FROM ( SELECT * FROM ABC )",
            "SELECT  ID,A,B,C  FROM ( SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC )",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q2", null,
            "SELECT * FROM ( SELECT * FROM TABLE_A ), ( SELECT * FROM TABLE_B )",
            "SELECT  ID_A,NAME_A,ID_B,NAME_B  FROM ( SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A ), ( SELECT  TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_B )",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q3", null, "SELECT A.* FROM ( SELECT * FROM TABLE_A ) AS A",
            "SELECT  \"A\".ID_A,\"A\".NAME_A  FROM ( SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A ) AS A",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q4", null,
            "SELECT A.*, B.* FROM ( SELECT * FROM TABLE_A ) AS A, ( SELECT * FROM TABLE_B ) AS B",
            "SELECT  \"A\".ID_A,\"A\".NAME_A , \"B\".ID_B,\"B\".NAME_B  FROM ( SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A ) AS A, ( SELECT  TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_B ) AS B", null);
    }


         /**
     *  checks views which are defined using a column list
     */
    private void checkColumnLists()  {

        // just to ensure the column count handling is as expected, else below tests might be useless
        TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, "CREATE VIEW IMPOSSIBLE (\"A\") AS SELECT * FROM ABC");

        // ................................................................
        // not that it should make any difference to S1, but who knows
        checkViewTranslationAndContent("L1", new String[] {
            "C1", "C2", "C3", "C4"
        }, "SELECT * FROM ABC",
           "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
           "ABC");
    }

         /**
     *  checks views based on other views
     */
    private void checkViewsOnViews() {

        // ................................................................
        // not that it should make any difference whether we SELECT FROM a table or view, but who knows
        checkViewTranslationAndContent(
            "V1", null, "SELECT * FROM S1",
            "SELECT  \"S1\".ID,\"S1\".A,\"S1\".B,\"S1\".C  FROM S1", "L1");
    }

        /**
     *  checks views based on a UNION statement
     */
    private void checkUnionViews()  {

        checkViewTranslationAndContent(
            "U1", null, "SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B",
            "SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A UNION SELECT  TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_B",
            new Object[][] {
            new Object[] {
                (1), "first A"
            }, new Object[] {
                (1), "first B"
            }, new Object[] {
                (2), "second A"
            }, new Object[] {
                (2), "second B"
            }
        });
        checkViewTranslationAndContent(
            "U2", null,
            "SELECT * FROM ( SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B )",
            "SELECT  ID_A,NAME_A  FROM ( SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A UNION SELECT  TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_B )",
            new Object[][] {
            new Object[] {
                (1), "first A"
            }, new Object[] {
                (1), "first B"
            }, new Object[] {
                (2), "second A"
            }, new Object[] {
                (2), "second B"
            }
        });
    }

    }
}
